{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2dafdf11-4849-49d6-b3bb-ce0c2ab605a8",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.2.89\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tushare as ts\n",
    "import json\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "print(ts.__version__)\n",
    "pro = ts.pro_api('ea3263c5424f08c3e04d605af9458cfc349613d5b7c27e99994eb396')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6674feb9-6d5a-4517-96a6-f0aa2277970b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7\n",
      "1\n",
      "[2, 1, 2, 1, 1, 1]\n",
      "1\n",
      "[2, 2, 1, 1, 1]\n",
      "1\n",
      "[2, 2, 1, 1]\n",
      "1\n",
      "[2, 2, 1]\n",
      "[2, 2, 1]\n",
      "n= 3\n"
     ]
    }
   ],
   "source": [
    "x=[1,2,1,2,1,1,1]\n",
    "n=len(x)\n",
    "print(n)\n",
    "for i in x:\n",
    "    n=n-1\n",
    "    print(i)\n",
    "    if i==1:\n",
    "        x.remove(i)\n",
    "    print(x)\n",
    "print(x)\n",
    "print(\"n=\",n)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d2eb8302-1515-49a9-9d5a-352bb073d410",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>exchange</th>\n",
       "      <th>cal_date</th>\n",
       "      <th>is_open</th>\n",
       "      <th>pretrade_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20230730</td>\n",
       "      <td>0</td>\n",
       "      <td>20230728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20230729</td>\n",
       "      <td>0</td>\n",
       "      <td>20230728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20230728</td>\n",
       "      <td>1</td>\n",
       "      <td>20230727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20230727</td>\n",
       "      <td>1</td>\n",
       "      <td>20230726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20230726</td>\n",
       "      <td>1</td>\n",
       "      <td>20230725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2892</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20150829</td>\n",
       "      <td>0</td>\n",
       "      <td>20150828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2893</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20150828</td>\n",
       "      <td>1</td>\n",
       "      <td>20150827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2894</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20150827</td>\n",
       "      <td>1</td>\n",
       "      <td>20150826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2895</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20150826</td>\n",
       "      <td>1</td>\n",
       "      <td>20150825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2896</th>\n",
       "      <td>SSE</td>\n",
       "      <td>20150825</td>\n",
       "      <td>1</td>\n",
       "      <td>20150824</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2897 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     exchange  cal_date  is_open pretrade_date\n",
       "0         SSE  20230730        0      20230728\n",
       "1         SSE  20230729        0      20230728\n",
       "2         SSE  20230728        1      20230727\n",
       "3         SSE  20230727        1      20230726\n",
       "4         SSE  20230726        1      20230725\n",
       "...       ...       ...      ...           ...\n",
       "2892      SSE  20150829        0      20150828\n",
       "2893      SSE  20150828        1      20150827\n",
       "2894      SSE  20150827        1      20150826\n",
       "2895      SSE  20150826        1      20150825\n",
       "2896      SSE  20150825        1      20150824\n",
       "\n",
       "[2897 rows x 4 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import datetime\n",
    "from dateutil.relativedelta import relativedelta\n",
    "current_time=datetime.datetime.now().strftime('%Y%m%d')\n",
    "#end=(current_time+datetime.timedelta(weeks = -26)).strftime('%Y%m%d')\n",
    "\n",
    "\n",
    "#获取交易日历\n",
    "df = pro.trade_cal(exchange='', start_date='20150825', end_date=current_time)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d423620e-5c01-4ff5-bbb6-ef1711b9d692",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "209"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import psycopg2\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "\n",
    "# establish connections\n",
    "uri= 'postgresql://hbu:@127.0.0.1:5432/hbu'\n",
    "  \n",
    "engine = create_engine(uri)\n",
    "#conn=engine.connect()\n",
    "df.to_sql('trade_cal', engine, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "e284bbc3-d6fe-4d50-9927-0eaaf38857db",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/mh/l6_b7c0x7m3bq41r5m25snqc0000gn/T/ipykernel_13121/102908901.py:2: FutureWarning: Inferring datetime64[ns] from data containing strings is deprecated and will be removed in a future version. To retain the old behavior explicitly pass Series(data, dtype=datetime64[ns])\n",
      "  df=pd.read_excel(fn,index_col=None, header=0)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>transfer_day</th>\n",
       "      <th>transfer_time</th>\n",
       "      <th>atrribute</th>\n",
       "      <th>inflow</th>\n",
       "      <th>outflow</th>\n",
       "      <th>amount</th>\n",
       "      <th>chect_at</th>\n",
       "      <th>assets</th>\n",
       "      <th>profit</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>comment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-08-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>1500.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3500.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-10-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>5500.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9000.0</td>\n",
       "      <td>2015-10-27</td>\n",
       "      <td>8896.70</td>\n",
       "      <td>-114.41</td>\n",
       "      <td>9011.11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-11-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-11-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>500.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11500.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>2023-06-26</td>\n",
       "      <td>09:22:48</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>612.06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>131000.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>2023-06-29</td>\n",
       "      <td>10:15:20</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>2500.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>133500.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>2023-07-03</td>\n",
       "      <td>09:53:49</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>131000.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>2023-07-13</td>\n",
       "      <td>09:23:28</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>130000.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>2023-07-18</td>\n",
       "      <td>10:06:17</td>\n",
       "      <td>建行存管</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7000.0</td>\n",
       "      <td>123000.0</td>\n",
       "      <td>2022-07-28</td>\n",
       "      <td>126239.89</td>\n",
       "      <td>-131000.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>209 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    transfer_day transfer_time atrribute   inflow  outflow    amount  \\\n",
       "0     2015-08-25           NaN      建行存管  1500.00      NaN    1500.0   \n",
       "1     2015-09-02           NaN      建行存管  2000.00      NaN    3500.0   \n",
       "2     2015-10-12           NaN      建行存管  5500.00      NaN    9000.0   \n",
       "3     2015-11-25           NaN      建行存管  2000.00      NaN   11000.0   \n",
       "4     2015-11-27           NaN      建行存管   500.00      NaN   11500.0   \n",
       "..           ...           ...       ...      ...      ...       ...   \n",
       "204   2023-06-26      09:22:48      建行存管   612.06      0.0  131000.0   \n",
       "205   2023-06-29      10:15:20      建行存管  2500.00      0.0  133500.0   \n",
       "206   2023-07-03      09:53:49      建行存管     0.00   2500.0  131000.0   \n",
       "207   2023-07-13      09:23:28      建行存管     0.00   1000.0  130000.0   \n",
       "208   2023-07-18      10:06:17      建行存管     0.00   7000.0  123000.0   \n",
       "\n",
       "      chect_at     assets     profit  total_amount comment  \n",
       "0          NaT        NaN        NaN           NaN     NaN  \n",
       "1          NaT        NaN        NaN           NaN     NaN  \n",
       "2   2015-10-27    8896.70    -114.41       9011.11     NaN  \n",
       "3          NaT        NaN        NaN           NaN     NaN  \n",
       "4          NaT        NaN        NaN           NaN     NaN  \n",
       "..         ...        ...        ...           ...     ...  \n",
       "204        NaT        NaN        NaN           NaN     NaN  \n",
       "205        NaT        NaN        NaN           NaN     NaN  \n",
       "206        NaT        NaN        NaN           NaN     NaN  \n",
       "207        NaT        NaN        NaN           NaN     NaN  \n",
       "208 2022-07-28  126239.89 -131000.00           NaN     NaN  \n",
       "\n",
       "[209 rows x 11 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fn='/Users/hbu/demo/stock-cash-flow.xlsx'\n",
    "df=pd.read_excel(fn,index_col=None, header=0)\n",
    "df.to_sql('cf_account', engine, index=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a082a2f3-3c02-4e77-ab5c-32a31b4e7dfb",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
